﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Data;
    using System.Data.Common;
    using WMS6.Common;
    using WMS6.DBUtility;

    public class GenNextNumberDA
    {
        public static string GenNextNumStr(string sTableName, string whLoginID)
        {
            string sSQL = ("SELECT KEY_NAME, COLUMN_NAME, ALPHA_PREFIX, NEXT_NUMBER, ALPHA_SUFFIX, LEADING_ZEROS, TOTAL_LENGTH, IS_DATE_BASED, IS_REF_TO_TABLE, UPDATED_DATE, CONVERT(CHAR(10), UPDATED_DATE, 121) AS UPDATEDATE, CONVERT(CHAR(10), GETDATE(), 121) AS SERVERDATE FROM @_@NEXT_NUMBER_GEN WHERE KEY_NAME=" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
            DataTable dt = new DataTable();
            dt = DBHelper.ExecuteDataSet(CommandType.Text, sSQL).Tables[0];
            if (dt.Rows.Count == 1)
            {
                int iNextNumber;
                if ((dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y") && (Convert.ToDateTime(dt.Rows[0]["UPDATEDATE"]) < Convert.ToDateTime(dt.Rows[0]["SERVERDATE"])))
                {
                    iNextNumber = 1;
                    sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=1, UPDATED_DATE=GETDATE() WHERE KEY_NAME=" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
                    DBHelper.ExecuteNonQuery(CommandType.Text, sSQL);
                }
                else
                {
                    iNextNumber = Convert.ToInt32(dt.Rows[0]["NEXT_NUMBER"]) + 1;
                    sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=" + StrUtils.FormatSQLInt(iNextNumber) + ", UPDATED_DATE=GETDATE() WHERE KEY_NAME =" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
                    DBHelper.ExecuteNonQuery(CommandType.Text, sSQL);
                }
                if (dt.Rows[0]["IS_REF_TO_TABLE"].ToString().ToUpper() == "Y")
                {
                    return GenString(iNextNumber, sTableName, Convert.ToString(dt.Rows[0]["COLUMN_NAME"]), Convert.ToString(dt.Rows[0]["ALPHA_PREFIX"]), Convert.ToString(dt.Rows[0]["ALPHA_SUFFIX"]), dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y", dt.Rows[0]["LEADING_ZEROS"].ToString().ToUpper() == "Y", Convert.ToDateTime(dt.Rows[0]["SERVERDATE"]), Convert.ToInt32(dt.Rows[0]["TOTAL_LENGTH"]), whLoginID);
                }
                string sRtnString = Convert.ToString(dt.Rows[0]["ALPHA_PREFIX"]);
                if (dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y")
                {
                    sRtnString = sRtnString + Convert.ToDateTime(dt.Rows[0]["SERVERDATE"]).ToString("YYMMDD");
                }
                if (dt.Rows[0]["LEADING_ZEROS"].ToString().ToUpper() == "Y")
                {
                    sRtnString = sRtnString + LeadingFormat(iNextNumber.ToString(), Convert.ToInt32(dt.Rows[0]["TOTAL_LENGTH"]), '0');
                }
                else
                {
                    sRtnString = sRtnString + iNextNumber.ToString();
                }
                return (sRtnString + dt.Rows[0]["ALPHA_SUFFIX"].ToString().ToUpper());
            }
            return "";
        }

        public static string GenNextNumStr(DataBase dataBase, DbTransaction tran, string sTableName, string whLoginID)
        {
            string sSQL = ("SELECT KEY_NAME, COLUMN_NAME, ALPHA_PREFIX, NEXT_NUMBER, ALPHA_SUFFIX, LEADING_ZEROS, TOTAL_LENGTH, IS_DATE_BASED, IS_REF_TO_TABLE, UPDATED_DATE, CONVERT(CHAR(10), UPDATED_DATE, 121) AS UPDATEDATE, CONVERT(CHAR(10), GETDATE(), 121) AS SERVERDATE FROM @_@NEXT_NUMBER_GEN WHERE KEY_NAME=" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
            DataTable dt = new DataTable();
            dt = dataBase.ExecuteDataSet(tran, CommandType.Text, sSQL).Tables[0];
            if (dt.Rows.Count == 1)
            {
                int iNextNumber;
                if ((dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y") && (Convert.ToDateTime(dt.Rows[0]["UPDATEDATE"]) < Convert.ToDateTime(dt.Rows[0]["SERVERDATE"])))
                {
                    iNextNumber = 1;
                    sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=1, UPDATED_DATE=GETDATE() WHERE KEY_NAME=" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
                    dataBase.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                }
                else
                {
                    iNextNumber = Convert.ToInt32(dt.Rows[0]["NEXT_NUMBER"]) + 1;
                    sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=" + StrUtils.FormatSQLInt(iNextNumber) + ", UPDATED_DATE=GETDATE() WHERE KEY_NAME =" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
                    dataBase.ExecuteNonQuery(tran, CommandType.Text, sSQL);
                }
                if (dt.Rows[0]["IS_REF_TO_TABLE"].ToString().ToUpper() == "Y")
                {
                    return GenString(dataBase, tran, iNextNumber, sTableName, Convert.ToString(dt.Rows[0]["COLUMN_NAME"]), Convert.ToString(dt.Rows[0]["ALPHA_PREFIX"]), Convert.ToString(dt.Rows[0]["ALPHA_SUFFIX"]), dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y", dt.Rows[0]["LEADING_ZEROS"].ToString().ToUpper() == "Y", Convert.ToDateTime(dt.Rows[0]["SERVERDATE"]), Convert.ToInt32(dt.Rows[0]["TOTAL_LENGTH"]), whLoginID);
                }
                string sRtnString = Convert.ToString(dt.Rows[0]["ALPHA_PREFIX"]);
                if (dt.Rows[0]["IS_DATE_BASED"].ToString().ToUpper() == "Y")
                {
                    sRtnString = sRtnString + Convert.ToDateTime(dt.Rows[0]["SERVERDATE"]).ToString("YYMMDD");
                }
                if (dt.Rows[0]["LEADING_ZEROS"].ToString().ToUpper() == "Y")
                {
                    sRtnString = sRtnString + LeadingFormat(iNextNumber.ToString(), Convert.ToInt32(dt.Rows[0]["TOTAL_LENGTH"]), '0');
                }
                else
                {
                    sRtnString = sRtnString + iNextNumber.ToString();
                }
                return (sRtnString + dt.Rows[0]["ALPHA_SUFFIX"].ToString().ToUpper());
            }
            return "";
        }

        private static string GenString(int iNumber, string sTableName, string sColumnName, string sAlphaPrefix, string sAlphaSufFix, bool bDateBased, bool bLeadZero, DateTime sServerDate, int iTotalLenth, string whLoginID)
        {
            string sTemp = sAlphaPrefix;
            if (bDateBased)
            {
                sTemp = sTemp + sServerDate.ToString("yyMMdd");
            }
            if (bLeadZero)
            {
                sTemp = sTemp + LeadingFormat(iNumber.ToString(), iTotalLenth, '0');
            }
            else
            {
                sTemp = sTemp + iNumber;
            }
            sTemp = sTemp + sAlphaSufFix;
            string sSQL = "SELECT" + StrUtils.FormatSQLStr(sColumnName) + " FROM " + whLoginID + sTableName + " WHERE " + StrUtils.FormatSQLStr(sColumnName) + " =" + StrUtils.FormatSQLStr(sTemp);
            DataTable dt = new DataTable();
            dt = DBHelper.ExecuteDataSet(CommandType.Text, sSQL).Tables[0];
            if (dt.Rows.Count != 0)
            {
                return GenString(iNumber + 1, sTableName, sColumnName, sAlphaPrefix, sAlphaSufFix, bDateBased, bLeadZero, sServerDate, iTotalLenth, whLoginID);
            }
            sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=" + StrUtils.FormatSQLInt(iNumber) + ", UPDATED_DATE=GETDATE() WHERE KEY_NAME =" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
            DBHelper.ExecuteNonQuery(CommandType.Text, sSQL);
            return sTemp;
        }

        private static string GenString(DataBase dataBase, DbTransaction tran, int iNumber, string sTableName, string sColumnName, string sAlphaPrefix, string sAlphaSufFix, bool bDateBased, bool bLeadZero, DateTime sServerDate, int iTotalLenth, string whLoginID)
        {
            string sTemp = sAlphaPrefix;
            if (bDateBased)
            {
                sTemp = sTemp + sServerDate.ToString("yyMMdd");
            }
            if (bLeadZero)
            {
                sTemp = sTemp + LeadingFormat(iNumber.ToString(), iTotalLenth, '0');
            }
            else
            {
                sTemp = sTemp + iNumber;
            }
            sTemp = sTemp + sAlphaSufFix;
            string sSQL = "SELECT" + StrUtils.FormatSQLStr(sColumnName) + " FROM " + whLoginID + sTableName + " WHERE " + StrUtils.FormatSQLStr(sColumnName) + " =" + StrUtils.FormatSQLStr(sTemp);
            DataTable dt = new DataTable();
            dt = dataBase.ExecuteDataSet(tran, CommandType.Text, sSQL).Tables[0];
            if (dt.Rows.Count != 0)
            {
                return GenString(dataBase, tran, iNumber + 1, sTableName, sColumnName, sAlphaPrefix, sAlphaSufFix, bDateBased, bLeadZero, sServerDate, iTotalLenth, whLoginID);
            }
            sSQL = ("UPDATE @_@NEXT_NUMBER_GEN SET NEXT_NUMBER=" + StrUtils.FormatSQLInt(iNumber) + ", UPDATED_DATE=GETDATE() WHERE KEY_NAME =" + StrUtils.FormatSQLStr(sTableName)).Replace("@_@", whLoginID);
            dataBase.ExecuteNonQuery(tran, CommandType.Text, sSQL);
            return sTemp;
        }

        public static int GetNextIdentity(string sTableName, int iNextIdent, int iIdentInc)
        {
            if (GetTableIdentity(sTableName, iNextIdent, iIdentInc))
            {
                return iNextIdent;
            }
            return iNextIdent;
        }

        public static bool GetTableIdentity(string aTable, int iCurrentIdent, int iIdentInc)
        {
            string sSQL = "SELECT SCOPE_IDENTITY() AS IDENT_CURR, IDENT_INCR(' + FormatSQLStr(aTable) + ') AS INCR";
            DataTable dt = new DataTable();
            dt = DBHelper.ExecuteDataSet(CommandType.Text, sSQL).Tables[0];
            if (dt.Rows.Count == 1)
            {
                iCurrentIdent = Convert.ToInt32(dt.Rows[0]["IDENT_CURR"]);
                iIdentInc = Convert.ToInt32(dt.Rows[0]["INCR"]);
                return true;
            }
            return false;
        }

        public static string LeadingFormat(string aStr, int aLen, char achar)
        {
            int i = aStr.Length;
            if (i > aLen)
            {
                return aStr;
            }
            return (new string(achar, aLen - i) + aStr);
        }
    }
}

